/*     */ package com.zimbra.cs.db;
/*     */ 
/*     */ import com.zimbra.common.localconfig.DebugConfig;
/*     */ import com.zimbra.common.service.ServiceException;
/*     */ import com.zimbra.common.util.Log;
/*     */ import com.zimbra.common.util.Pair;
/*     */ import com.zimbra.common.util.ZimbraLog;
/*     */ import com.zimbra.cs.account.DataSource;
/*     */ import com.zimbra.cs.datasource.imap.ImapFolder;
/*     */ import com.zimbra.cs.datasource.imap.ImapMessage;
/*     */ import com.zimbra.cs.datasource.imap.ImapMessageCollection;
/*     */ import com.zimbra.cs.mailbox.Flag;
/*     */ import com.zimbra.cs.mailbox.MailItem.Type;
/*     */ import com.zimbra.cs.mailbox.Mailbox;
/*     */ import java.sql.PreparedStatement;
/*     */ import java.sql.ResultSet;
/*     */ import java.sql.SQLException;
/*     */ import java.util.ArrayList;
/*     */ import java.util.List;
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ public class DbImapMessage
/*     */ {
/*     */   public static final String TABLE_IMAP_MESSAGE = "imap_message";
/*     */   
/*     */   public static void storeImapMessage(Mailbox mbox, int localFolderId, long remoteUid, int localItemId, int flags)
/*     */     throws ServiceException
/*     */   {
/*  48 */     ZimbraLog.datasource.debug("Storing IMAP message tracker: mboxId=%d, localFolderId=%d, remoteUid=%d, localItemId=%d flags=%s", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(localFolderId), Long.valueOf(remoteUid), Integer.valueOf(localItemId), Flag.toString(flags) });
/*     */     
/*     */ 
/*     */ 
/*  52 */     DbPool.DbConnection conn = null;
/*  53 */     PreparedStatement stmt = null;
/*     */     try {
/*  55 */       conn = DbPool.getConnection(mbox);
/*     */       
/*  57 */       stmt = conn.prepareStatement("INSERT INTO " + getTableName(mbox) + " (" + DbMailItem.MAILBOX_ID + "imap_folder_id, uid, item_id, flags) " + "VALUES (" + DbMailItem.MAILBOX_ID_VALUE + "?, ?, ?, ?)");
/*     */       
/*     */ 
/*     */ 
/*  61 */       int pos = 1;
/*  62 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/*  63 */       stmt.setInt(pos++, localFolderId);
/*  64 */       stmt.setLong(pos++, remoteUid);
/*  65 */       stmt.setInt(pos++, localItemId);
/*  66 */       stmt.setInt(pos++, flags);
/*  67 */       stmt.executeUpdate();
/*  68 */       conn.commit();
/*     */     } catch (SQLException e) {
/*  70 */       throw ServiceException.FAILURE("Unable to store IMAP message data", e);
/*     */     } finally {
/*  72 */       DbPool.closeStatement(stmt);
/*  73 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */   public static void setUid(Mailbox mbox, int itemId, long uid)
/*     */     throws ServiceException
/*     */   {
/*  80 */     ZimbraLog.datasource.debug("Updating IMAP message tracker uid: mboxId=%d, localItemId=%d remoteUid=%x", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(itemId), Long.valueOf(uid) });
/*     */     
/*     */ 
/*     */ 
/*  84 */     DbPool.DbConnection conn = null;
/*  85 */     PreparedStatement stmt = null;
/*     */     try {
/*  87 */       conn = DbPool.getConnection(mbox);
/*     */       
/*  89 */       stmt = conn.prepareStatement("UPDATE " + getTableName(mbox) + " SET uid = ?" + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "item_id = ?");
/*     */       
/*     */ 
/*  92 */       int pos = 1;
/*  93 */       stmt.setLong(pos++, uid);
/*  94 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/*  95 */       stmt.setInt(pos++, itemId);
/*  96 */       stmt.executeUpdate();
/*  97 */       conn.commit();
/*     */     } catch (SQLException e) {
/*  99 */       throw ServiceException.FAILURE("Unable to update IMAP message data", e);
/*     */     } finally {
/* 101 */       if (stmt != null) DbPool.closeStatement(stmt);
/* 102 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */   public static long getMinUid(Mailbox mbox, int folderId) throws ServiceException {
/* 107 */     return getMinMaxUid(mbox, folderId, "min");
/*     */   }
/*     */   
/*     */   public static long getMaxUid(Mailbox mbox, int folderId) throws ServiceException {
/* 111 */     return getMinMaxUid(mbox, folderId, "max");
/*     */   }
/*     */   
/*     */   private static long getMinMaxUid(Mailbox mbox, int folderId, String minmax) throws ServiceException
/*     */   {
/* 116 */     ZimbraLog.datasource.debug("Getting %s IMAP uid: mboxId=%d, folderId=%d", new Object[] { minmax, Integer.valueOf(mbox.getId()), Integer.valueOf(folderId) });
/*     */     
/*     */ 
/* 119 */     DbPool.DbConnection conn = null;
/* 120 */     PreparedStatement stmt = null;
/* 121 */     ResultSet rs = null;
/*     */     try {
/* 123 */       conn = DbPool.getConnection();
/* 124 */       stmt = conn.prepareStatement(String.format("SELECT %s FROM %s WHERE mailbox_id = %d AND imap_folder_id = %d AND uid > 0", new Object[] { minmax, getTableName(mbox), Integer.valueOf(mbox.getId()), Integer.valueOf(folderId) }));
/*     */       
/*     */ 
/* 127 */       rs = stmt.executeQuery();
/* 128 */       return rs.next() ? rs.getLong(1) : -1L;
/*     */     } catch (SQLException e) {
/* 130 */       throw ServiceException.FAILURE("Unable to execute query", e);
/*     */     } finally {
/* 132 */       DbPool.closeResults(rs);
/* 133 */       DbPool.closeStatement(stmt);
/* 134 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */   public static void setFlags(Mailbox mbox, int itemId, int flags) throws ServiceException
/*     */   {
/* 140 */     ZimbraLog.datasource.debug("Updating IMAP message tracker flags: mboxId=%d, localItemId=%d flags=%s", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(itemId), Flag.toString(flags) });
/*     */     
/*     */ 
/* 143 */     DbPool.DbConnection conn = null;
/* 144 */     PreparedStatement stmt = null;
/*     */     try {
/* 146 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 148 */       stmt = conn.prepareStatement("UPDATE " + getTableName(mbox) + " SET flags = ?" + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "item_id = ?");
/*     */       
/*     */ 
/* 151 */       int pos = 1;
/* 152 */       stmt.setInt(pos++, flags);
/* 153 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 154 */       stmt.setInt(pos++, itemId);
/* 155 */       stmt.executeUpdate();
/* 156 */       conn.commit();
/*     */     } catch (SQLException e) {
/* 158 */       throw ServiceException.FAILURE("Unable to update IMAP message data", e);
/*     */     } finally {
/* 160 */       if (stmt != null) DbPool.closeStatement(stmt);
/* 161 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static void deleteImapMessage(Mailbox mbox, int localFolderId, int localItemId)
/*     */     throws ServiceException
/*     */   {
/* 171 */     ZimbraLog.datasource.debug("Deleting IMAP message tracker: mboxId=%d, localFolderId=%d, msgId=%d", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(localFolderId), Integer.valueOf(localItemId) });
/*     */     
/*     */ 
/*     */ 
/* 175 */     DbPool.DbConnection conn = null;
/* 176 */     PreparedStatement stmt = null;
/*     */     try {
/* 178 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 180 */       stmt = conn.prepareStatement("DELETE FROM " + getTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "imap_folder_id = ? AND item_id = ?");
/*     */       
/*     */ 
/* 183 */       int pos = 1;
/* 184 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 185 */       stmt.setInt(pos++, localFolderId);
/* 186 */       stmt.setInt(pos++, localItemId);
/* 187 */       stmt.executeUpdate();
/* 188 */       conn.commit();
/*     */     } catch (SQLException e) {
/* 190 */       throw ServiceException.FAILURE("Unable to delete IMAP message data", e);
/*     */     } finally {
/* 192 */       DbPool.closeStatement(stmt);
/* 193 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static void deleteImapMessages(Mailbox mbox, int localFolderId)
/*     */     throws ServiceException
/*     */   {
/* 203 */     ZimbraLog.datasource.debug("Deleting all IMAP message trackers: mboxId=%d, localFolderId=%d", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(localFolderId) });
/*     */     
/*     */ 
/* 206 */     DbPool.DbConnection conn = null;
/* 207 */     PreparedStatement stmt = null;
/*     */     try {
/* 209 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 211 */       stmt = conn.prepareStatement("DELETE FROM " + getTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "imap_folder_id = ?");
/*     */       
/*     */ 
/* 214 */       int pos = 1;
/* 215 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 216 */       stmt.setInt(pos++, localFolderId);
/* 217 */       stmt.executeUpdate();
/* 218 */       conn.commit();
/*     */     } catch (SQLException e) {
/* 220 */       throw ServiceException.FAILURE("Unable to delete IMAP message data", e);
/*     */     } finally {
/* 222 */       DbPool.closeStatement(stmt);
/* 223 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */   public static int getLocalMessageId(Mailbox mbox, int localFolderId, long remoteUid)
/*     */     throws ServiceException
/*     */   {
/* 232 */     ZimbraLog.datasource.debug("Getting local message id for tracked message: mboxId=%d, localFolderId=%d, remoteUid=%d", new Object[] { Integer.valueOf(mbox.getId()), Integer.valueOf(localFolderId), Long.valueOf(remoteUid) });
/*     */     
/*     */ 
/*     */ 
/* 236 */     DbPool.DbConnection conn = null;
/* 237 */     PreparedStatement stmt = null;
/* 238 */     ResultSet rs = null;
/*     */     try {
/* 240 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 242 */       stmt = conn.prepareStatement("SELECT item_id FROM " + getTableName(mbox) + " WHERE " + DbMailItem.IN_THIS_MAILBOX_AND + "imap_folder_id = ? AND uid = ?");
/*     */       
/*     */ 
/*     */ 
/* 246 */       int pos = 1;
/* 247 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 248 */       stmt.setInt(pos++, localFolderId);
/* 249 */       stmt.setLong(pos++, remoteUid);
/* 250 */       rs = stmt.executeQuery();
/* 251 */       return rs.next() ? rs.getInt(1) : 0;
/*     */     } catch (SQLException e) {
/* 253 */       throw ServiceException.FAILURE("Unable to get IMAP message data", e);
/*     */     } finally {
/* 255 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */   public static Pair<ImapMessage, Integer> getImapMessage(Mailbox mbox, DataSource ds, int itemId) throws ServiceException
/*     */   {
/* 261 */     DbPool.DbConnection conn = null;
/* 262 */     PreparedStatement stmt = null;
/* 263 */     ResultSet rs = null;
/*     */     try {
/* 265 */       conn = DbPool.getConnection();
/*     */       
/* 267 */       String MBOX_JOIN = DebugConfig.disableMailboxGroups ? "" : "imap.mailbox_id = mi.mailbox_id AND";
/* 268 */       String IN_IMAP_MAILBOX_AND = DebugConfig.disableMailboxGroups ? "" : "imap.mailbox_id = ? AND";
/* 269 */       stmt = conn.prepareStatement("SELECT imap.uid, imap.flags as tflags, imap.imap_folder_id, mi.unread, mi.flags FROM " + getTableName(mbox) + " imap " + " LEFT OUTER JOIN " + DbMailItem.getMailItemTableName(mbox) + " mi " + " ON " + MBOX_JOIN + " imap.item_id = mi.id " + "WHERE " + IN_IMAP_MAILBOX_AND + " imap.item_id = ?");
/*     */       
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/* 275 */       int pos = 1;
/* 276 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 277 */       stmt.setInt(pos++, itemId);
/* 278 */       rs = stmt.executeQuery();
/* 279 */       long uid; if (rs.next()) {
/* 280 */         uid = rs.getLong("uid");
/* 281 */         int flags = rs.getInt("flags");
/* 282 */         int unread = rs.getInt("unread");
/* 283 */         int tflags = rs.getInt("tflags");
/* 284 */         int folderId = rs.getInt("imap_folder_id");
/* 285 */         flags = unread > 0 ? flags | Flag.BITMASK_UNREAD : flags & (Flag.BITMASK_UNREAD ^ 0xFFFFFFFF);
/* 286 */         return new Pair(new ImapMessage(ds, -1, itemId, tflags, uid, flags), Integer.valueOf(folderId));
/*     */       }
/* 288 */       return null;
/*     */     } catch (SQLException e) {
/* 290 */       throw ServiceException.FAILURE("Unable to get IMAP message data", e);
/*     */     } finally {
/* 292 */       DbPool.closeResults(rs);
/* 293 */       DbPool.closeStatement(stmt);
/* 294 */       DbPool.quietClose(conn);
/*     */     }
/*     */   }
/*     */   
/*     */   public static List<ImapMessage> getMovedMessages(Mailbox mbox, DataSource ds, int folderId) throws ServiceException
/*     */   {
/* 300 */     List<ImapMessage> msgs = new ArrayList();
/*     */     
/* 302 */     DbPool.DbConnection conn = null;
/* 303 */     PreparedStatement stmt = null;
/* 304 */     ResultSet rs = null;
/*     */     try {
/* 306 */       conn = DbPool.getConnection();
/* 307 */       stmt = conn.prepareStatement(String.format("SELECT imap.uid, imap.item_id, imap.flags as tflags, mi.unread, mi.flags  FROM %s imap JOIN %s mi  ON imap.mailbox_id = mi.mailbox_id AND imap.item_id = mi.id  WHERE imap.mailbox_id = %d AND imap.imap_folder_id = %d AND mi.folder_id != %d", new Object[] { getTableName(mbox), DbMailItem.getMailItemTableName(mbox), Integer.valueOf(mbox.getId()), Integer.valueOf(folderId), Integer.valueOf(folderId) }));
/*     */       
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/* 316 */       rs = stmt.executeQuery();
/* 317 */       while (rs.next()) {
/* 318 */         long uid = rs.getLong("uid");
/* 319 */         int itemId = rs.getInt("item_id");
/* 320 */         int flags = rs.getInt("flags");
/* 321 */         int unread = rs.getInt("unread");
/* 322 */         int tflags = rs.getInt("tflags");
/* 323 */         flags = unread > 0 ? flags | Flag.BITMASK_UNREAD : flags & (Flag.BITMASK_UNREAD ^ 0xFFFFFFFF);
/* 324 */         msgs.add(new ImapMessage(ds, -1, itemId, tflags, uid, flags));
/*     */       }
/* 326 */       rs.close();
/* 327 */       stmt.close();
/*     */     } catch (SQLException e) {
/* 329 */       throw ServiceException.FAILURE("Unable to get IMAP message data", e);
/*     */     } finally {
/* 331 */       DbPool.closeResults(rs);
/* 332 */       DbPool.closeStatement(stmt);
/* 333 */       DbPool.quietClose(conn);
/*     */     }
/* 335 */     return msgs;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */   public static ImapMessageCollection getImapMessages(Mailbox mbox, DataSource ds, ImapFolder imapFolder)
/*     */     throws ServiceException
/*     */   {
/* 344 */     ImapMessageCollection imapMessages = new ImapMessageCollection();
/*     */     
/* 346 */     DbPool.DbConnection conn = null;
/* 347 */     PreparedStatement stmt = null;
/* 348 */     ResultSet rs = null;
/*     */     try {
/* 350 */       String MBOX_JOIN = DebugConfig.disableMailboxGroups ? "" : "imap.mailbox_id = mi.mailbox_id AND";
/* 351 */       String IN_IMAP_MAILBOX_AND = DebugConfig.disableMailboxGroups ? "" : "imap.mailbox_id = ? AND";
/* 352 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 354 */       stmt = conn.prepareStatement("SELECT imap.uid, imap.item_id, imap.flags as tflags, mi.unread, mi.flags FROM " + getTableName(mbox) + " imap " + "  LEFT OUTER JOIN " + DbMailItem.getMailItemTableName(mbox) + " mi " + "  ON " + MBOX_JOIN + " imap.item_id = mi.id " + "WHERE " + IN_IMAP_MAILBOX_AND + " imap.imap_folder_id = ?");
/*     */       
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/* 360 */       int pos = 1;
/* 361 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 362 */       stmt.setInt(pos++, imapFolder.getItemId());
/* 363 */       rs = stmt.executeQuery();
/*     */       
/* 365 */       while (rs.next()) {
/* 366 */         long uid = rs.getLong("uid");
/* 367 */         int itemId = rs.getInt("item_id");
/* 368 */         int flags = rs.getInt("flags");
/* 369 */         int unread = rs.getInt("unread");
/* 370 */         int tflags = rs.getInt("tflags");
/* 371 */         flags = unread > 0 ? flags | Flag.BITMASK_UNREAD : flags & (Flag.BITMASK_UNREAD ^ 0xFFFFFFFF);
/* 372 */         imapMessages.add(new ImapMessage(ds, imapFolder.getItemId(), itemId, tflags, uid, flags));
/*     */       }
/*     */     } catch (SQLException e) {
/* 375 */       throw ServiceException.FAILURE("Unable to get IMAP message data", e);
/*     */     } finally {
/* 377 */       DbPool.closeResults(rs);
/* 378 */       DbPool.closeStatement(stmt);
/* 379 */       DbPool.quietClose(conn);
/*     */     }
/*     */     
/* 382 */     ZimbraLog.datasource.debug("Found %d tracked IMAP messages for %s", new Object[] { Integer.valueOf(imapMessages.size()), imapFolder.getRemoteId() });
/*     */     
/* 384 */     return imapMessages;
/*     */   }
/*     */   
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/*     */   public static List<Integer> getNewLocalMessageIds(Mailbox mbox, int folderId)
/*     */     throws ServiceException
/*     */   {
/* 397 */     List<Integer> newIds = new ArrayList();
/*     */     
/* 399 */     DbPool.DbConnection conn = null;
/* 400 */     PreparedStatement stmt = null;
/* 401 */     ResultSet rs = null;
/*     */     try {
/* 403 */       String MBOX_JOIN = DebugConfig.disableMailboxGroups ? "" : "imap.mailbox_id = mi.mailbox_id AND";
/* 404 */       String IN_MI_MAILBOX_AND = DebugConfig.disableMailboxGroups ? "" : "mi.mailbox_id = ? AND";
/* 405 */       conn = DbPool.getConnection(mbox);
/*     */       
/* 407 */       stmt = conn.prepareStatement("SELECT id FROM " + DbMailItem.getMailItemTableName(mbox) + " mi " + "  LEFT OUTER JOIN " + getTableName(mbox) + " imap " + "  ON " + MBOX_JOIN + " imap.item_id = mi.id " + "WHERE " + IN_MI_MAILBOX_AND + " mi.folder_id = ? AND imap.item_id IS NULL " + "AND mi.type IN (" + MailItem.Type.MESSAGE.toByte() + ", " + MailItem.Type.CHAT.toByte() + ")");
/*     */       
/*     */ 
/*     */ 
/*     */ 
/*     */ 
/* 413 */       int pos = 1;
/* 414 */       pos = DbMailItem.setMailboxId(stmt, mbox, pos);
/* 415 */       stmt.setInt(pos, folderId);
/* 416 */       rs = stmt.executeQuery();
/*     */       
/* 418 */       while (rs.next())
/* 419 */         newIds.add(Integer.valueOf(rs.getInt("id")));
/*     */     } catch (SQLException e) {
/* 421 */       throw ServiceException.FAILURE("Unable to get new local message ids", e);
/*     */     } finally {
/* 423 */       DbPool.closeResults(rs);
/* 424 */       DbPool.closeStatement(stmt);
/* 425 */       DbPool.quietClose(conn);
/*     */     }
/*     */     
/* 428 */     return newIds;
/*     */   }
/*     */   
/*     */   public static String getTableName(int mailboxId, int groupId) {
/* 432 */     return DbMailbox.qualifyTableName(groupId, "imap_message");
/*     */   }
/*     */   
/*     */   public static String getTableName(Mailbox mbox) {
/* 436 */     return DbMailbox.qualifyTableName(mbox, "imap_message");
/*     */   }
/*     */ }


/* Location:              /home/mint/zimbrastore.jar!/com/zimbra/cs/db/DbImapMessage.class
 * Java compiler version: 7 (51.0)
 * JD-Core Version:       0.7.1
 */